﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Web.UI.WebControls;
using System.Web.UI;
using System.Web.Mvc;
using System.Reflection;
using System.Diagnostics;
using QGZX.Helper;
using MODEL.ViewModel;
using MODEL;
using System.Data.SqlClient;
using System.Configuration;

namespace QGZX.Logic.Zmjh
{
   
    public class TestExcelController : Controller
    {
         public ActionResult index()
        {
            return View();
        }


        #region  Excel导入+  public ActionResult Upload()
         /// <summary>
         /// 文件上传
         /// </summary>
         /// <returns></returns>
         [HttpPost]
         public ActionResult Upload()
         {
             ExcelHelper helper = new ExcelHelper();
             ///获得文件列表中excel的每一个对象的第一个sheet并转换成datatable
             //List<DataTable> dtList = helper.SaveExcelAndReadExcel(Request);
             //导入excel--只接受excel2003-2007格式
             List<qgzx_zm_course> listModel = helper.SaveExcelAndReadExcel<qgzx_zm_course>(Request);
             //Excel表的导入，第一行要写上数据库的字段名，要完全一致，否则数据导入会出错。

             ///**********在这里编写保存泛型对象列表到数据库的语句，并在界面上提示导入成功************
             try
             {
                 foreach (qgzx_zm_course coursemodel in listModel)
                 {

                     //往course表里新增一条记录
                     int res = OperateContext.Current.BLLSession.Iqgzx_zm_courseBLL.Add(coursemodel);
                     if (res > 0)
                         continue;

                     else
                         return OperateContext.Current.RedirectAjax("ok", "插入表格出错，请重新导入表格~", null, "");



                 }
             }
             catch (Exception ex)
             {
                 throw new Exception(ex.Message);
             }



             return Redirect("index");
         } 
         #endregion

        //#region 另外一种方法 +从System.Data.DataTable导入数据到数据库
        ///// <summary>
        ///// 从System.Data.DataTable导入数据到数据库
        ///// </summary>
        ///// <param name="dt"></param>
        ///// <returns></returns>
        //public int InsetData(System.Data.DataTable dt)
        //{
        //    int i = 0;
        //    string lng = "";
        //    string lat = "";
        //    string offsetLNG = "";
        //    string offsetLAT = "";

        //    foreach (DataRow dr in dt.Rows)
        //    {
        //        lng = dr["LNG"].ToString().Trim();
        //        lat = dr["LAT"].ToString().Trim();
        //        offsetLNG = dr["OFFSET_LNG"].ToString().Trim();
        //        offsetLAT = dr["OFFSET_LAT"].ToString().Trim();

        //        //sw = string.IsNullOrEmpty(sw) ? "null" : sw;
        //        //kr = string.IsNullOrEmpty(kr) ? "null" : kr;

        //        string strSql = string.Format("Insert into DBToExcel (LNG,LAT,OFFSET_LNG,OFFSET_LAT) Values ('{0}','{1}',{2},{3})", lng, lat, offsetLNG, offsetLAT);

        //        string strConnection = ConfigurationManager.ConnectionStrings["ConnectionStr"].ToString();
        //        SqlConnection sqlConnection = new SqlConnection(strConnection);
        //        try
        //        {
        //            // SqlConnection sqlConnection = new SqlConnection(strConnection);
        //            sqlConnection.Open();
        //            SqlCommand sqlCmd = new SqlCommand();
        //            sqlCmd.CommandText = strSql;
        //            sqlCmd.Connection = sqlConnection;
        //            SqlDataReader sqlDataReader = sqlCmd.ExecuteReader();
        //            i++;
        //            sqlDataReader.Close();
        //        }
        //        catch (Exception ex)
        //        {
        //            throw ex;
        //        }
        //        finally
        //        {
        //            sqlConnection.Close();

        //        }
        //        //if (opdb.ExcSQL(strSql))
        //        //    i++;
        //    }
        //    return i;
        //} 
        //#endregion
    
       
        #region ------------------Excel已经完成导出的数据-----------修改测试----------------
        /// <summary>
        /// 文件下载
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpPost]
        public ActionResult Download(qgzx_zm_apply model)
        {
            //String CourseName = Request.QueryString["CourseName"];
            String CourseName = "心理健康";
            ExcelHelper helper = new ExcelHelper();
            //**************************此段为测试代码**********************************
            DataTable dt = new DataTable();

            var list = OperateContext.Current.BLLSession.Iqgzx_zm_applyBLL.GetListBy(x => x.FirstCourseName == CourseName, m => m.ApplyId);
            //foreach循环list集合，每一个list再根据id查询另外一张表的数据，把需要的字段追加进来
            
            DataColumn col = new DataColumn("ID");//导出excel表头
            DataColumn col1 = new DataColumn("课程名称");
            DataColumn col2 = new DataColumn("课程大纲");
            DataColumn col3 = new DataColumn("课程目的");
            DataColumn col4 = new DataColumn("课程介绍");
            DataColumn col5 = new DataColumn("是否开启");
            DataColumn col6 = new DataColumn("姓名");
            dt.Columns.Add(col);
            dt.Columns.Add(col1);
            dt.Columns.Add(col2);
            dt.Columns.Add(col3);
            dt.Columns.Add(col4);
            dt.Columns.Add(col5);
            dt.Columns.Add(col6);

            foreach (qgzx_zm_apply item in list)
            {
                qgzx_student_information stumodel = OperateContext.Current.BLLSession.Iqgzx_student_informationBLL.GetListBy(x => x.StudentId == item.StudentId, y => y.StudentId).FirstOrDefault();

                DataRow dr = dt.NewRow();
                dr[0] = item.ApplyId.ToString();
                dr[1] = item.ApplyTime.ToString();
                dr[2] = item.FirstCourseName.ToString();
                dr[3] = item.SecondCourseName.ToString();
                dr[4] = item.IsChosed.ToString();
                dr[5] = item.Period.ToString();
                dr[6] = stumodel.StudentName.ToString();
                dt.Rows.Add(dr);
            }
            //********************************************************************
            //导出excel--默认excel2003-2007
            helper.ExportExcel(dt, Response);
            return new EmptyResult();
        }
        #endregion

    }



  

}


